﻿using System;
using ADOX;
using ADODB;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
    public class DatasetToJetConverter
    {
        static public void CopyDatasetToJetDB(DataSet ds, string jetFileName)
        {
            string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4;"
                + "Data Source='" + jetFileName + "';";
            Catalog cat = new CatalogClass();

            try
            {
                cat.Create(connstr);
                ADODB.Connection conn = cat.ActiveConnection as ADODB.Connection; 
                foreach (DataTable table in ds.Tables)
                {
                    try
                    {
                        cat.Tables.Delete(table.TableName);
                    }
                    catch { }
                    ADOX.Table adoxTab = CopyDataTable(table, cat);
                    cat.Tables.Append(adoxTab);
                    MoveData(adoxTab, table);
                }
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
            }
            finally
            {
                cat = null;
            }            
        }

        static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
        {
            ADOX.Table adoxTable = new ADOX.TableClass();
            adoxTable.Name = table.TableName;
            adoxTable.ParentCatalog = cat;
            foreach (System.Data.DataColumn col in table.Columns)
            {
                ADOX.Column adoxCol = new ADOX.ColumnClass();
                adoxCol.ParentCatalog = cat;
                adoxCol.Name = col.ColumnName;
                adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
                adoxCol.Attributes = (adoxCol.Type != ADOX.DataTypeEnum.adBoolean) ? ADOX.ColumnAttributesEnum.adColNullable : ADOX.ColumnAttributesEnum.adColFixed;

                if (col.MaxLength >= 0)
                    adoxCol.DefinedSize = col.MaxLength;
                if (adoxCol.Type == ADOX.DataTypeEnum.adVarWChar)
                    adoxCol.DefinedSize = 255;

                adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
            }
            return adoxTable;
        }

        static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
        {
            string guid = type.GUID.ToString();
            ADOX.DataTypeEnum adoxType =
             guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean :
             guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt :
             guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar :
             guid == typeof(DateTime).GUID.ToString() ? ADOX.DataTypeEnum.adDate :
             guid == typeof(decimal).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :
             guid == typeof(double).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :
             guid == typeof(Int16).GUID.ToString() ? ADOX.DataTypeEnum.adSmallInt :
             guid == typeof(Int32).GUID.ToString() ? ADOX.DataTypeEnum.adInteger :
             guid == typeof(Int64).GUID.ToString() ? ADOX.DataTypeEnum.adBigInt :
             guid == typeof(SByte).GUID.ToString() ? ADOX.DataTypeEnum.adTinyInt :
             guid == typeof(Single).GUID.ToString() ? ADOX.DataTypeEnum.adSingle :
             guid == typeof(string).GUID.ToString() ? ADOX.DataTypeEnum.adVarWChar :
             guid == typeof(TimeSpan).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :
             guid == typeof(UInt16).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedSmallInt :
             guid == typeof(UInt32).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedInt :
             guid == typeof(UInt64).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedBigInt :
             ADOX.DataTypeEnum.adVarBinary;
            return adoxType;
        }

        private static ADODB.Command ADOXTableInsertCommand(ADOX.Table adoxTab)
        {
            ADODB.Command result = new ADODB.CommandClass();
            object conn = adoxTab.ParentCatalog.ActiveConnection;
            result.ActiveConnection = conn as ADODB.Connection;
            result.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2}) ", adoxTab.Name, "{0}", "{1}");
            string colNames = string.Empty;
            string colVals = string.Empty;
            for (int i = 0; i < adoxTab.Columns.Count; i++)
            {
                ADOX.Column adoxCol = adoxTab.Columns[i];
                string name = adoxCol.Name;
                ADOX.DataTypeEnum type = adoxCol.Type;
                switch (type)
                {
                    case ADOX.DataTypeEnum.adVarBinary: break;
                    default:
                        colNames += (colNames != string.Empty ? ", " : "") + "[" + name + "]";
                        colVals += (colVals != string.Empty ? ", " : "") + "?";
                        break;
                }
            }
            result.CommandText = string.Format(result.CommandText, colNames, colVals);
            return result;
        }

        private static void MoveData(ADOX.Table adoxTab, DataTable aTable)
        {
            object i;
            ADODB.Command cmd = ADOXTableInsertCommand(adoxTab);
            foreach (DataRow row in aTable.Rows)
            {
                object[] array = new object[aTable.Columns.Count];
                for (int c = 0; c < aTable.Columns.Count; c++)
                    if (adoxTab.Columns[c].Type == ADOX.DataTypeEnum.adDate)
                        array[c] = string.Format("{0:dd/MM/yyyy HH:mm:ss}", Convert.ToDateTime(row[(string)adoxTab.Columns[c].Name], System.Globalization.CultureInfo.GetCultureInfo("ru-RU")));
                    else
                        array[c] = row[(string)adoxTab.Columns[c].Name];

                object arry = array;
                cmd.Execute(out i, ref arry, 1);
            }
        }



    }
}